package com.yc.dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author UNO
 * 2021年6月26日上午1:15:40
 */
public class DBHelper {

	//相关对象
	private Connection conn = null;//连接对象
	private PreparedStatement pstmt = null;//预编译
	private ResultSet rs = null;

	//只在类中第一次加载才执行 且 执行一次
	static {
		// 加载注册依赖
		try {
			Class.forName(ReadConfig.getInstance().getProperty("driverClassName"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	//建立连接
	private Connection getConn(){
		try {
			conn = DriverManager.getConnection(ReadConfig.getInstance().getProperty("url"), ReadConfig.getInstance());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	/**
	 * 关闭资源
	 */
	@SuppressWarnings("unamed")
	private void closeAll(){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
		if(pstmt != null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 更新操作 Object  ...params 不定参数 类似于数组 万物皆对象
	 * @param sql   要执行的更新语句 可以 insert update delete
	 * @param params  要执行的SQL语句中的占位符？ 所对应参数的值
	 * @return
	 */
	public int update(String sql, Object ...params){
		int result = -1;//定义默认范围值
		try {
			conn = this.getConn(); 
			pstmt = conn.prepareStatement(sql);//预编译对象装载SQL
			this.setParams(pstmt,params);//需要给占位符注入参数
			result = pstmt.executeUpdate();//执行更新语句
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public int update(String sql, List<Object> params) {
		if(params == null || params.isEmpty()) {//若干占位符列表参数为空
			return this.update(sql);
		}
		return this.update(sql, params.toArray());
	}
	/**
	 * 给预编译语句块中占位符 赋值 设置参数
	 * @param pstmt 预编译对象
	 * @param params  需要执行的SQL语句中的占位符？ 所对应参数的值
	 * @throws SQLException 
	 */
	private void setParams(PreparedStatement pstmt, Object ...params)  {
		// TODO 去空判断 说明没有参数给我 也就是执行的SQL语句中没有占位符
		if(null == params || params.length <= 0){
			return;
		}
		for(int i = 0, len = params.length; i < len; i ++)
			try {
				pstmt.setObject(i + 1, params[i]);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				//项目可以使用日志来记录
				System.out.println("第" + (i+1) + "个参数注值失败...");
			}
	}
	/**
	 * 单条查询 返回一条记录 select * from userinfo where user_name = ? and user_pwd = ?;
	 * @param sql  查询SQL
	 * @param params  查询参数
	 * @return  map 一条记录
	 */
	public Map<String, Object> findSingle(String sql, Object ...params) {
		Map<String, Object> map = null;
		try {
			conn = this.getConn();
			pstmt = conn.prepareStatement(sql);
			this.setParams(pstmt, params);
			rs = pstmt.executeQuery();
			//获取所有的列名
			List<String> columnNames = this.getAllColumnNames(rs);
			if(rs.next()){
				map = new HashMap<String, Object>();
				//				map.put("user_id", rs.getInt("user_id"));
				//				map.put("user_id", rs.getInt("user_id"));
				//				map.put("user_id", rs.getInt("user_id"));
				Object value = null;//列所对应的值
				String type = "";//列所对应的值的类型
				//增强for
				for(String columnName : columnNames){
					//列对应的类型不确定
					//map.put(columnName, rs.getObject(columnName));
					value = rs.getObject(columnName);
					//判断是否为空
					if(null == value){
						map.put(columnName, value);
						continue;
					}
					type = value.getClass().getName();//获取类型
					
					//判断类型
					if("oracle.sql.BLOB".equals(type)){
						Blob blob = rs.getBlob(columnName);
						try(InputStream is = blob.getBinaryStream()){
							byte [] bt = new byte[(int)blob.length()];
							is.read(bt);
							map.put(columnName, bt);
						} catch (IOException e) {
							e.printStackTrace();
						}
					}else{
						map.put(columnName, value);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return map;
	}
	public Map<String, Object> findSingle(String sql, List<Object> params) {
		if(params == null || params.isEmpty()) {//若干占位符列表参数为空
			return this.findSingle(sql);
		}
		return this.findSingle(sql, params.toArray());
	}
	/**
	 * 获取所有的列名
	 * @param rs2
	 * @return
	 */
	private List<String> getAllColumnNames(ResultSet rs) {
		//存储列的集合
		List<String> list = new ArrayList<String>();
		try {
			//getMetaDate() 获取此 ResultSet 对象的列的编号、类型和地址
			ResultSetMetaData  rsmd = rs.getMetaData();
			//获取的范围的数量
			int count = rsmd.getColumnCount();
			//列的范围确定 循坏
			for(int i = 1; i <= count; i++){
				list.add(rsmd.getColumnName(i).toLowerCase());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 多行查询 返回多条 select * from emp;
	 * @param sql   查询SQL
	 * @param params  查询参数
	 * @return  list	
	 */
	public List<Map<String, Object>> findMultiple(String sql, Object ...params) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();

		Map<String, Object> map = null;

		try {
			conn = this.getConn();
			pstmt = conn.prepareStatement(sql);
			this.setParams(pstmt, params);
			rs = pstmt.executeQuery();
			List<String> columnNames = this.getAllColumnNames(rs);

			while(rs.next()){
				map = new HashMap<String, Object>();
				Object value = null;//列所对应的值
				String type = "";
//				map.put("user_id", rs.getInt("user_id"));
//				map.put("user_name", rs.getString("user_name"));
//				map.put("user_pwd", rs.getString("user_pwd"));
				for(String columnName : columnNames){
					//列对应的类型不确定
					//map.put(columnName, rs.getObject(columnName));
					value = rs.getObject(columnName);
					//判断是否为空
					if(null == value){
						map.put(columnName, value);
						continue;
					}
					type = value.getClass().getName();//获取类型
					
					//判断类型
					if("oracle.sql.BLOB".equals(type)){
						Blob blob = rs.getBlob(columnName);
						try(InputStream is = blob.getBinaryStream()){
							byte [] bt = new byte[(int)blob.length()];
							is.read(bt);
							map.put(columnName, bt);
						} catch (IOException e) {
							e.printStackTrace();
						}
					}else{
						map.put(columnName, value);
					}

				}
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
	public List<Map<String, Object>> findMultiple(String sql, List<Object> params) {
		if(params == null || params.isEmpty()) {//若干占位符列表参数为空
			return this.findMultiple(sql);
		}
		return this.findMultiple(sql, params.toArray());
	}
	/**
	 * 多行查询 返回多条 select * from emp;
	 * @param sql   查询SQL
	 * @param params  查询参数
	 * @return  list	
	 */
	public List<Map<String, String>> finds(String sql, Object ...params) {
		List<Map<String, String>> list = new ArrayList<Map<String,String>>();

		Map<String, String> map = null;

		try {
			conn = this.getConn();
			pstmt = conn.prepareStatement(sql);
			this.setParams(pstmt, params);
			rs = pstmt.executeQuery();
			List<String> columnNames = this.getAllColumnNames(rs);
			while(rs.next()){
				map = new HashMap<String, String>();
				for(String columnName : columnNames){

					map.put(columnName, rs.getString(columnName));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}
	public List<Map<String, String>> finds(String sql, List<Object> params) {
		if(params == null || params.isEmpty()) {//若干占位符列表参数为空
			return this.finds(sql);
		}
		return this.finds(sql, params.toArray());
	}
}